package com.aaa.book_mall.dao.impl;

import com.aaa.book_mall.dao.RecordDao;
import com.aaa.book_mall.entity.Record;
import com.aaa.book_mall.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * fileName:RecordDaoImpl
 * description:
 * author:付亚星
 * createTime:2021/1/28 22:16
 * version:1.0.0
 */
@Repository
public class RecordDaoImpl implements RecordDao {


    @Override
    public List<Map<String, Object>> getAllRecord(Record record) {
        System.out.println("查询所有入库信息记录");
        StringBuilder sql = new StringBuilder();
        sql.append("select tro.rid,tro.number,tro.staffid,tro.staffname,tro.bookcount,tro.bookprice, tro.ruku_date from t_rukurecord tro INNER JOIN t_request tre ON tro.number=tre.number where tre.status = '1'");
        String startdate="";
        String enddate="";
        System.out.println(".....................");
//        当筛选条件有日期时，对日期进行分割
        if(record.getScopedate()!=null && !"".equals(record.getScopedate())){
            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
            String scopedate = record.getScopedate();
//           截取开始时间
            startdate = scopedate.substring(0, 19);
            System.out.println(startdate);
            enddate = scopedate.substring(21);
            System.out.println(enddate);
        }

//          根据条件拼接sql语句
        if(record.getScopedate()!=null && !"".equals(record.getScopedate()) && record.getStaffname()!=null && !"".equals(record.getStaffname())){
            sql.append(" and tro.ruku_date >'"+startdate+"' and tro.ruku_date <'"+enddate+"' and tro.staffname like '%"+record.getStaffname()+"%'");
        }else if(record.getScopedate()!=null && !"".equals(record.getScopedate())){
            sql.append(" and tro.ruku_date >'"+startdate+"' and tro.ruku_date <'"+enddate+"'");
        }else if(record.getStaffname()!=null && !"".equals(record.getStaffname())){
            sql.append(" and tro.staffname like '%"+record.getStaffname()+"%'");
        }else {
            System.out.println("limit");
            sql.append(" limit ?,?");
        }
        System.out.println(sql);
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql.toString(),(record.getPage() - 1) * record.getLimit(), record.getLimit());
        return maps;

    }

    @Override
    public int insertRecord(Record record) {
        String sql = "insert into t_rukurecord values(null,?,?,?,?,?,?)";
        int update = JDBCUtil.update(sql, record.getNumber(), record.getStaffid(), record.getStaffname(), record.getBookcount(), record.getBookprice(), record.getRuke_date());
        return update;
    }

    @Override
    public int queryCountAllInfo() {
        String sql="select count(*) from t_rukurecord tr INNER JOIN t_request trq ON tr.number=trq.number where trq.status='1'";
        List<List> lists = JDBCUtil.queryList(sql);
        int count=0;
        if(lists.size()>0){
            count=Integer.parseInt(lists.get(0).get(0).toString());
        }
        return count;
    }

    /**
     * 查询入库总金额
     * @param scopDate
     * @return
     */
    @Override
    public List<List> selectInMoney(String scopDate) {
        String startdate="";
        String enddate="";
        System.out.println(".....................");
        boolean b=false;
//        当筛选条件有日期时，对日期进行分割
        if(scopDate!=null && !"".equals(scopDate)){
            System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
//           截取开始时间
            startdate = scopDate.substring(0, 7);
            System.out.println(startdate);
            enddate = scopDate.substring(22,29);
            System.out.println(enddate);
            if(startdate.equals(enddate)){
                b=true;
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT CONCAT(YEAR(createdate),'-',MONTH(createdate)) as ymcreatedate, sum(bookprice) price from t_request where `status`=1");
        if(!"".equals(scopDate) && scopDate!=null){
            sql.append(" and createdate > '"+startdate+"' and createdate < '"+enddate+"'");
        }else if(!"".equals(scopDate) && scopDate!=null && b){
            sql.append(" and createdate ='%"+startdate+"%'");
        }
        sql.append(" GROUP BY ymcreatedate");
        System.out.println(sql);
        List<List> lists = JDBCUtil.queryList(sql.toString());//查询的结果
        System.out.println("InMoney");
        System.out.println(lists);
        return lists;
    }
}
